package db

import (
	"crypto/rand"
	"crypto/sha256"
	"database/sql"
	"encoding/base64"
	"encoding/hex"
	"fmt"
	"os"
	"path/filepath"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

type FileRecord struct {
	SourcePath  string
	TargetPath  string
	StartTime   int64 // 视频开始时间戳（秒）
	EndTime     int64 // 视频结束时间戳（秒）
	ProcessedAt string
}

type Database struct {
	db *sql.DB
}

func NewDatabase(dbPath string) (*Database, error) {
	// 确保数据库目录存在
	dbDir := filepath.Dir(dbPath)
	if err := os.MkdirAll(dbDir, 0755); err != nil {
		return nil, err
	}

	db, err := sql.Open("sqlite3", dbPath)
	if err != nil {
		return nil, err
	}

	database := &Database{db: db}

	// 创建处理文件表
	_, err = db.Exec(`
		CREATE TABLE IF NOT EXISTS processed_files (
			source_path TEXT PRIMARY KEY,
			target_path TEXT,
			start_time INTEGER,
			end_time INTEGER,
			processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
		)
	`)
	if err != nil {
		db.Close()
		return nil, err
	}

	// 初始化用户表
	if err := database.initUserTable(); err != nil {
		db.Close()
		return nil, err
	}

	// 创建默认管理员账户
	if err := database.createDefaultAdmin(); err != nil {
		db.Close()
		return nil, err
	}

	return database, nil
}

func (d *Database) Close() error {
	return d.db.Close()
}

func (d *Database) IsFileProcessed(sourcePath string) (bool, error) {
	var count int
	err := d.db.QueryRow("SELECT COUNT(*) FROM processed_files WHERE source_path = ?", sourcePath).Scan(&count)
	if err != nil {
		return false, err
	}
	return count > 0, nil
}

func (d *Database) RecordProcessedFile(sourcePath, targetPath string, startTime, endTime time.Time) error {
	_, err := d.db.Exec(
		`INSERT INTO processed_files 
		(source_path, target_path, start_time, end_time) 
		VALUES (?, ?, ?, ?)`,
		sourcePath,
		targetPath,
		startTime.Unix(), // 转换为Unix时间戳（秒）
		endTime.Unix(),   // 转换为Unix时间戳（秒）
	)
	return err
}

// 查询特定时间范围的视频
func (d *Database) QueryVideosByTimeRange(start, end time.Time) ([]FileRecord, error) {
	rows, err := d.db.Query(`
		SELECT source_path, target_path, start_time, end_time, processed_at 
		FROM processed_files 
		WHERE start_time >= ? AND end_time <= ?
	`, start.Unix(), end.Unix())
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var records []FileRecord
	for rows.Next() {
		var record FileRecord
		err := rows.Scan(
			&record.SourcePath,
			&record.TargetPath,
			&record.StartTime,
			&record.EndTime,
			&record.ProcessedAt,
		)
		if err != nil {
			return nil, err
		}
		records = append(records, record)
	}
	return records, nil
}

// 添加计数方法
func (d *Database) CountVideosByTimeRange(start, end time.Time, cameraType string) (int, error) {
	query := `
		SELECT COUNT(*) 
		FROM processed_files 
		WHERE start_time >= ? AND end_time <= ?
	`
	args := []interface{}{start.Unix(), end.Unix()}

	if cameraType != "" {
		query += " AND target_path LIKE ?"
		args = append(args, fmt.Sprintf("%%%s%%", cameraType))
	}

	var count int
	err := d.db.QueryRow(query, args...).Scan(&count)
	return count, err
}

// 添加分页查询方法
func (d *Database) QueryVideosByTimeRangeWithPaging(start, end time.Time, cameraType string, page, pageSize int) ([]FileRecord, error) {
	offset := (page - 1) * pageSize
	query := `
		SELECT source_path, target_path, start_time, end_time, processed_at 
		FROM processed_files 
		WHERE start_time >= ? AND end_time <= ?
	`
	args := []interface{}{start.Unix(), end.Unix()}

	if cameraType != "" {
		query += " AND target_path LIKE ?"
		args = append(args, fmt.Sprintf("%%%s%%", cameraType))
	}

	query += " ORDER BY start_time DESC LIMIT ? OFFSET ?"
	args = append(args, pageSize, offset)

	rows, err := d.db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var records []FileRecord
	for rows.Next() {
		var record FileRecord
		err := rows.Scan(
			&record.SourcePath,
			&record.TargetPath,
			&record.StartTime,
			&record.EndTime,
			&record.ProcessedAt,
		)
		if err != nil {
			return nil, err
		}
		records = append(records, record)
	}
	return records, nil
}

// 用户结构体
type User struct {
	Username string
	Password string
	Salt     string
}

// 创建用户表
func (d *Database) initUserTable() error {
	_, err := d.db.Exec(`
		CREATE TABLE IF NOT EXISTS users (
			username TEXT PRIMARY KEY,
			password TEXT NOT NULL,
			salt TEXT NOT NULL
		)
	`)
	return err
}

// 创建默认管理员账户
func (d *Database) createDefaultAdmin() error {
	// 检查是否已存在管理员账户
	var count int
	err := d.db.QueryRow("SELECT COUNT(*) FROM users WHERE username = ?", "admin").Scan(&count)
	if err != nil {
		return err
	}
	if count > 0 {
		return nil
	}

	// 创建默认管理员账户
	salt := generateSalt()                           // 实现这个函数生成随机盐值
	hashedPassword := hashPassword("admin123", salt) // 实现这个函数进行密码哈希
	_, err = d.db.Exec(
		"INSERT INTO users (username, password, salt) VALUES (?, ?, ?)",
		"admin", hashedPassword, salt,
	)
	return err
}

// 验证用户登录
func (d *Database) ValidateUser(username, password string) (bool, error) {
	var storedPassword, salt string
	err := d.db.QueryRow(
		"SELECT password, salt FROM users WHERE username = ?",
		username,
	).Scan(&storedPassword, &salt)

	if err == sql.ErrNoRows {
		return false, nil
	}
	if err != nil {
		return false, err
	}

	hashedPassword := hashPassword(password, salt)
	return hashedPassword == storedPassword, nil
}

// 生成随机盐值
func generateSalt() string {
	bytes := make([]byte, 16)
	rand.Read(bytes)
	return base64.StdEncoding.EncodeToString(bytes)
}

// 密码哈希
func hashPassword(password, salt string) string {
	hash := sha256.New()
	hash.Write([]byte(password + salt))
	return hex.EncodeToString(hash.Sum(nil))
}

// 添加修改密码方法
func (d *Database) ChangePassword(username, oldPassword, newPassword string) error {
	// 先验证旧密码
	valid, err := d.ValidateUser(username, oldPassword)
	if err != nil {
		return err
	}
	if !valid {
		return fmt.Errorf("旧密码不正确")
	}

	// 生成新的盐值和密码哈希
	salt := generateSalt()
	hashedPassword := hashPassword(newPassword, salt)

	// 更新数据库
	_, err = d.db.Exec(
		"UPDATE users SET password = ?, salt = ? WHERE username = ?",
		hashedPassword, salt, username,
	)
	return err
}
